/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.aptech.dao;

import com.aptech.beans.Drugs;
import com.aptech.utility.DBType;
import com.aptech.utility.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author Anh Nguyen
 */
public class DrugManager {
    public static ResultSet displayAllRows() throws SQLException{
        String sql = "SELECT Drugs.DrugID, Suppliers.SupplierName, Drugs.DrugName, Drugs.Unit, Drugs.UnitPrice, "
                + "Drugs.StockAvailable, Drugs.Description, Drugs.SupplierID" 
                + " FROM Drugs"
                + " INNER JOIN Suppliers"
                + " ON Drugs.SupplierID = Suppliers.SupplierID";
        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet resultSet = preparedStatement.executeQuery();
        return resultSet;
    }
    
    public static Drugs checkName(String DrugName) throws Exception{
        String sql = "SELECT * FROM Drugs WHERE DrugName = ?";
        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        preparedStatement.setString(1, DrugName);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            Drugs drug = new Drugs();
            drug.setDrugname(DrugName);
            return drug;
        } else {
            return null;
        }
    }
    
    public static boolean insert(Drugs drug) throws Exception{
        String sql = "INSERT INTO Drugs (DrugName, SupplierID, Unit, UnitPrice, StockAvailable, Description)"
                + "VALUES(?, ?, ?, ?, ?, ?)";
        Connection conn = DBUtil.getConnection(DBType.myConnection);
    //    conn.setAutoCommit(false);
        
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        preparedStatement.setString(1, drug.getDrugname());
        preparedStatement.setInt(2, drug.getSupplierID());
        preparedStatement.setString(3, drug.getUnit());
        preparedStatement.setDouble(4, drug.getUnitprice());
        preparedStatement.setInt(5, drug.getStockavailable());
        preparedStatement.setString(6, drug.getDescription());
        
        int affected = preparedStatement.executeUpdate();
    //    conn.commit();
        return affected == 1;
    } 
    
    public static boolean update(Drugs drug) throws Exception{
        String sql = "UPDATE Drugs SET SupplierID=?, DrugName=?, Unit=?, UnitPrice=?, StockAvailable=?, Description=? WHERE DrugID=?";
        
        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        
        preparedStatement.setInt(1, drug.getSupplierID());
        preparedStatement.setString(2, drug.getDrugname());
        preparedStatement.setString(3, drug.getUnit());
        preparedStatement.setDouble(4, drug.getUnitprice());
        preparedStatement.setInt(5, drug.getStockavailable());
        preparedStatement.setString(6, drug.getDescription());
        preparedStatement.setInt(7, drug.getDrugID());
      
        int affected = preparedStatement.executeUpdate();
        conn.commit();
        return affected == 1;
    }
    
    public static Drugs checkSuppIDByName(String Name) throws SQLException {
        String sql = "SELECT SupplierID FROM Suppliers WHERE SupplierName=?";
        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        preparedStatement.setString(1, Name);

        ResultSet resultSet = preparedStatement.executeQuery();

        if (resultSet.next()) {
            Drugs supplierName = new Drugs();
            supplierName.setSupplierID(resultSet.getInt(1));
            return supplierName;
        } else {
            return null;
        }
    }
    
    public static ResultSet getSuppID() throws SQLException {
        String sql = "SELECT SupplierID, SupplierName FROM Suppliers";
        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet resultSet = preparedStatement.executeQuery();

        return resultSet;
    }
}
